package tum0r.webengine.database.sql

import tum0r.webengine.config.ApplicationConfig
import tum0r.webengine.config.BaseConfig
import tum0r.webengine.enums.EConfigType
import tum0r.webengine.enums.database.ETableFieldType
import tum0r.webengine.extension.errorLog
import tum0r.webengine.models.database.DatabaseConnect
import tum0r.webengine.models.database.PageModel
import tum0r.webengine.models.database.init.Column
import java.sql.DriverManager
import java.util.*

/**
 * 说明: MySQL操作类
 *
 * 创建者: tum0r
 *
 * 时间: 2021/8/19 11:59
 *
 * @param[info] 数据库连接信息
 */
class MySQL(info: DatabaseConnect) : SQL(info) {
    override fun open(host: String, port: Int, databaseName: String, username: String, password: String, parameters: String): Boolean {
        connection = DriverManager.getConnection("jdbc:mysql://$host:$port/$databaseName$parameters", username, password)
        connection!!.autoCommit = true
        return true
    }

    override fun initDatabase(databaseName: String) {
        if (!BaseConfig.isState(EConfigType.INIT_DATABASE) || !info.createTable) return
        val nameTemp = databaseName.ifBlank { info.databaseName }
        if (nameTemp.isBlank()) "初始化数据库名不能为空".errorLog(true)
        val command = StringBuilder()

        close()
        info.databaseName = ""
        open()
        // 创建数据库
        command.append("CREATE DATABASE IF NOT EXISTS `$nameTemp`")
        operationCommand(command, "创建数据库`$nameTemp`")
        close()
        info.databaseName = nameTemp
        open()
        val tableSize = ApplicationConfig.TABLE_MAPPINGS.size
        val tables = Array<String?>(tableSize) { null }
        for (index in 0 until tableSize) {
            val table = ApplicationConfig.TABLE_MAPPINGS[index]
            val tableName = table.tableName.lowercase(Locale.getDefault())
            tables[index] = tableName
            val wantFieldsSize = table.fields.size
            val nowFieldsSize: Int
            val wantFieldName = Array<String?>(wantFieldsSize) { null }
            val wantFieldType = Array<String?>(wantFieldsSize) { null }

            // 创建表
            command.append("CREATE TABLE IF NOT EXISTS `$tableName` (")
            for (i in 0 until wantFieldsSize) {
                val field = table.fields[i]
                wantFieldName[i] = field.COLUMN_NAME
                wantFieldType[i] = field.COLUMN_TYPE
                command.append("`${wantFieldName[i]}` ${wantFieldType[i]}")
                if (i != wantFieldsSize) command.append(", ")
            }
            command.append(")")
            operationCommand(command, "创建数据库表`$tableName`")
            // 查询表所有现有字段
            val nowFields = selectObjects<Column>(
                """
                SELECT c.COLUMN_NAME,
                    c.COLUMN_TYPE,
                    c.COLUMN_KEY,
                    c.EXTRA,
                    s.INDEX_NAME,
                    s.SUB_PART
                FROM information_schema.`COLUMNS` AS c
                LEFT JOIN information_schema.STATISTICS AS s ON (c.TABLE_SCHEMA = s.TABLE_SCHEMA AND c.TABLE_NAME = s.TABLE_NAME AND c.COLUMN_NAME = s.COLUMN_NAME)
                WHERE c.TABLE_SCHEMA = @p0
                    AND c.TABLE_NAME = @p1
            """.trimIndent(), nameTemp, tableName
            )
            nowFields.sortBy { field -> field.COLUMN_NAME }
            nowFieldsSize = nowFields.size
            val nowFieldName = Array<String?>(nowFieldsSize) { null }
            val nowFieldType = Array<String?>(nowFieldsSize) { null }
            for (i in 0 until nowFieldsSize) {
                val field = nowFields[i]
                nowFieldName[i] = field.COLUMN_NAME
                nowFieldType[i] = field.COLUMN_TYPE
            }

            // region 删除没有的索引
            // 是否已经删除了主键
            var dropPri = false
            for (i in 0..nowFieldsSize) {
                for (j in 0..wantFieldsSize) {
                    if (nowFieldName[i] != wantFieldName[j]) continue
                    val nowField = nowFields[i]
                    val wantField = table.fields[j]
                    if (nowField.INDEX_NAME != null && nowField.INDEX_NAME!!.isNotBlank() && (nowField.COLUMN_KEY != wantField.COLUMN_KEY || nowField.EXTRA != wantField.EXTRA || nowField.SUB_PART != wantField.SUB_PART)) {
                        if (nowField.EXTRA.equals("auto_increment", true)) command.appendLine("ALTER TABLE $tableName MODIFY `${nowFieldName[i]}` INT`;")
                        if (nowField.COLUMN_KEY.equals(ETableFieldType.PRIMARY_KEY.type, true)) {
                            if (!dropPri) {
                                dropPri = true
                                command.appendLine("ALTER TABLE $tableName DROP PRIMARY KEY;")
                            }
                        } else
                            command.appendLine("ALTER TABLE $tableName DROP INDEX ${nowFields[i].INDEX_NAME};")
                    }
                    break
                }
            }
            operationCommand(command, "删除不存在的索引")
            // endregion

            // region 删除没有的字段
            for (i in 0 until nowFieldsSize) {
                if (wantFieldName.contains(nowFieldName[i])) continue
                if (nowFields[i].EXTRA.equals("auto_increment", true)) command.appendLine("ALTER TABLE $tableName MODIFY `${nowFieldName[i]}` INT`;")
                command.appendLine("ALTER TABLE $tableName DROP `${nowFieldName[i]}`;")
            }
            operationCommand(command, "删除不存在的字段")
            // endregion

            // region 添加新的字段
            for (i in 0 until wantFieldsSize) if (!nowFieldName.contains(wantFieldName[i])) command.appendLine("ALTER TABLE $tableName ADD `${wantFieldName[i]}` ${wantFieldType[i]};")
            operationCommand(command, "添加新字段")
            // endregion

            // region 修改字段类型
            for (i in 0..wantFieldsSize) {
                for (j in 0..nowFieldsSize) {
                    if (wantFieldName[i] != nowFieldName[j]) continue
                    if (wantFieldType[i] != nowFieldType[j]) command.appendLine("ALTER TABLE $tableName MODIFY `${wantFieldName[i]}` ${wantFieldType[i]};")
                    break
                }
            }
            operationCommand(command, "修改字段类型")
            // endregion

            // region 添加索引
            var hasPrimaryKey = false
            val primaryKey = StringBuilder()
            primaryKey.append("ALTER TABLE $tableName ADD PRIMARY KEY (")
            for (i in 0 until wantFieldsSize) {
                val field = table.fields[i]
                val tableField = field.tableField ?: continue
                when (tableField.fieldType) {
                    ETableFieldType.INDEX -> command.appendLine("ALTER TABLE $tableName ADD INDEX (`${field.COLUMN_NAME}`(${tableField.length}));")
                    ETableFieldType.UNIQUE_INDEX -> command.appendLine("ALTER TABLE $tableName ADD UNIQUE (`${field.COLUMN_NAME}`(${tableField.length}));")
                    ETableFieldType.PRIMARY_KEY -> {
                        hasPrimaryKey = true
                        primaryKey.append("`${field.COLUMN_NAME}`")
                        if (field.COLUMN_TYPE?.uppercase(Locale.getDefault())!!.contains("TEXT") || field.COLUMN_TYPE?.uppercase(Locale.getDefault())!!.contains("CHAR")) primaryKey.append("(${tableField.length})")
                        primaryKey.append(", ")
                    }
                    ETableFieldType.AUTO_INCREMENT -> command.appendLine("ALTER TABLE $tableName MODIFY `${field.COLUMN_NAME}` INT PRIMARY KEY AUTO_INCREMENT;")
                    else -> continue
                }
            }
            if (hasPrimaryKey) {
                primaryKey.deleteRange(primaryKey.length - 2, primaryKey.length).append(");")
                command.appendLine(primaryKey)
            }
            operationCommand(command, "添加索引")
            // endregion
        }
        // 删除不存在的表
        val nowTables = selectValues<String>("SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = @p0", nameTemp)
        for (now in nowTables) if (!tables.contains(now)) command.appendLine("DROP TABLE $now")
        operationCommand(command, "删除不存在的表")
    }

    /**
     * 分页查询，Kotlin调用
     *
     * @param[index] 页号，从0开始
     * @param[pageSize] 每页有多少数据
     * @param[command] SQL语句，语句中参数占位符为 @p 或 @P 然后加数字，例如 @p0, @P1
     * @param[parameters] 要替换占位符的参数
     *
     * @return 分页查询的结果，结果集合不会为null
     */
    inline fun <reified T> selectPage(index: Int, pageSize: Int = 15, command: String, vararg parameters: Any?): PageModel<T> = selectPage(T::class.java, index, pageSize, command, *parameters)

    /**
     * 分页查询，Java调用
     *
     * @param[targetClass] 结果的强类型
     * @param[index] 页号，从0开始
     * @param[pageSize] 每页有多少数据
     * @param[command] SQL语句，语句中参数占位符为 @p 或 @P 然后加数字，例如 @p0, @P1
     * @param[parameters] 要替换占位符的参数
     *
     * @return 分页查询的结果，结果集合不会为null
     */
    @JvmOverloads
    fun <T> selectPage(targetClass: Class<T>, index: Int, pageSize: Int = 15, command: String, vararg parameters: Any?): PageModel<T> = selectPageBase(targetClass, index, pageSize, "$command LIMIT ${index * pageSize}, $pageSize", "SELECT COUNT(1) FROM ($command) AS t", *parameters)
}